package mylab.starters.mybatis.utils;

import com.google.common.base.Joiner;
import com.google.common.collect.Lists;
import lombok.experimental.UtilityClass;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.ExpressionVisitor;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.util.TablesNamesFinder;
import net.sf.jsqlparser.util.deparser.ExpressionDeParser;
import net.sf.jsqlparser.util.deparser.SelectDeParser;
import net.sf.jsqlparser.util.deparser.UpdateDeParser;

import java.util.List;

@SuppressWarnings("unused")
@UtilityClass
public class JSqlParserUtil {

    /**
     * 为sql语句指定数据库名称
     *
     * @param schemeName 库名
     * @param sql        sql语句
     * @return 替换后的sql语句
     * @throws JSQLParserException JSQLParserException
     */
    public String replaceSchema(String schemeName, String sql) throws JSQLParserException {
        Statement statement = CCJSqlParserUtil.parse(sql);
        if (statement instanceof Insert) {
            Insert insert = (Insert) statement;
            return JSqlParserUtil.doInsert(insert, schemeName);
        } else if (statement instanceof Update) {
            Update update = (Update) statement;
            return JSqlParserUtil.doUpdate(update, schemeName);
        } else if (statement instanceof Delete) {
            Delete delete = (Delete) statement;
            return JSqlParserUtil.doDelete(delete, schemeName);
        } else if (statement instanceof Select) {
            Select select = (Select) statement;
            return JSqlParserUtil.doSelect(select, schemeName);
        }

        throw new RuntimeException("非法SQL语句 不可能执行到这里");
    }

    /**
     * 获取sql语句中的所有表名
     *
     * @param sql sql语句
     * @return 表名列表
     * @throws JSQLParserException JSQLParserException
     */
    public List<String> getTables(String sql) {
        try {
            Statement statement = CCJSqlParserUtil.parse(sql);
            Select selectStatement = (Select) statement;
            TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
            return tablesNamesFinder.getTableList(selectStatement);
        } catch (Exception e) {
            e.printStackTrace();
            return Lists.newArrayList();
        }
    }

    public String doSelect(Select select, String schemaName) {
        StringBuilder buffer = processSelect(schemaName, select);
        return buffer.toString();
    }

    public String doInsert(Insert insert, String schemaName) {
        StringBuilder buffer = new StringBuilder();
        Table t = insert.getTable();
        t.setSchemaName(schemaName);
        insert.setTable(t);
        Select select = insert.getSelect();
        if (select != null) {
            ExpressionDeParser expressionDeParser = new ExpressionDeParser();
            SQLParserSelect parser = new SQLParserSelect(expressionDeParser, buffer);
            parser.setSchemaName(schemaName);
            expressionDeParser.setSelectVisitor(parser);
            expressionDeParser.setBuffer(buffer);
            select.getSelectBody().accept(parser);
            insert.setSelect(select);
        }
        return insert.toString();
    }

    public String doUpdate(Update update, String schemaName) throws JSQLParserException {
        StringBuilder buffer = new StringBuilder();
        Table tb = update.getTable();
        tb.setSchemaName(schemaName);
        update.setTable(tb);
        // 处理from
        FromItem fromItem = update.getFromItem();
        if (fromItem != null) {
            Table tf = (Table) fromItem;
            tf.setSchemaName(schemaName);
        }
        // 处理join
        List<Join> joins = update.getJoins();
        if (joins != null && joins.size() > 0) {
            for (Object object : joins) {
                Join t = (Join) object;
                Table rightItem = (Table) t.getRightItem();
                rightItem.setSchemaName(schemaName);
                System.out.println();
            }
        }
        ExpressionDeParser expressionDeParser = new ExpressionDeParser();
        UpdateDeParser p = new UpdateDeParser(expressionDeParser, buffer);
        expressionDeParser.setBuffer(buffer);
        p.deParse(update);
        processWhere(schemaName, update.getWhere());
        return update.toString();
    }

    public String doDelete(Delete delete, String schemaName) throws JSQLParserException {
        Table t = delete.getTable();
        t.setSchemaName(schemaName);
        delete.setTable(t);
        processWhere(schemaName, delete.getWhere());
        return delete.toString();
    }

    private void processWhere(String schemaName, Expression where) throws JSQLParserException {
        if (where != null) {
            InExpression getRightItems = (InExpression) where;
            ItemsList rightItemsList = getRightItems.getRightItemsList();
            if (rightItemsList instanceof SubSelect) {
                SubSelect subSelect = (SubSelect) rightItemsList;
                SelectBody selectBody = subSelect.getSelectBody();
                Select select = (Select) CCJSqlParserUtil.parse(selectBody.toString());

                processSelect(schemaName, select);
                subSelect.setSelectBody(select.getSelectBody());
            }
        }
    }

    private StringBuilder processSelect(String schemaName, Select select) {
        StringBuilder buffer = new StringBuilder();
        ExpressionDeParser expressionDeParser = new ExpressionDeParser();
        SQLParserSelect parser = new SQLParserSelect(expressionDeParser, buffer);
        parser.setSchemaName(schemaName);
        expressionDeParser.setSelectVisitor(parser);
        expressionDeParser.setBuffer(buffer);
        select.getSelectBody().accept(parser);
        return buffer;
    }

    protected static class SQLParserSelect extends SelectDeParser {
        private String schemaName;

        public SQLParserSelect(ExpressionVisitor expressionVisitor, StringBuilder buffer) {
            super(expressionVisitor, buffer);
        }

        public String getSchemaName() {
            return schemaName;
        }

        public void setSchemaName(String schemaName) {
            this.schemaName = schemaName;
        }

        @Override
        public void visit(Table tableName) {
            tableName.setSchemaName(schemaName);
            StringBuilder buffer = getBuffer();
            buffer.append(tableName.getFullyQualifiedName());
            Pivot pivot = tableName.getPivot();
            if (pivot != null) {
                pivot.accept(this);
            }
            Alias alias = tableName.getAlias();
            if (alias != null) {
                buffer.append(alias);
            }
        }
    }

    public static void main(String[] args) throws JSQLParserException {
        String insert = JSqlParserUtil.doInsert((Insert) CCJSqlParserUtil.parse("Insert into article select  *  from bank"), "`member`");
        System.out.println(insert);
        String delete = JSqlParserUtil.doDelete((Delete) CCJSqlParserUtil.parse("delete from cap where id in (select p.user_id from per p left join test a on(a.id = b.id) inner join test t on(p.id = t.id) where t=1)"), "`rose`");
        System.out.println(delete);
        String txt = "SELECT o.* FROM unifiedorderclearing AS o\n" +
                "        LEFT JOIN unifiedorderclearingitem AS oi ON oi.orderId = o.id\n" +
                "        LEFT JOIN unifiedOrder as ou ON ou.id=o.orderId\n" +
                "        where 1=1 and o.username='test'";
        String select = JSqlParserUtil.doSelect((Select) CCJSqlParserUtil.parse(txt), "`eth`");
        System.out.println(select);

        String update = JSqlParserUtil.doUpdate((Update) CCJSqlParserUtil.parse("update cart set a=1, b=new() where id in (select p.user_id from per p left join test a on(a.id = b.id) inner join test t on(p.id = t.id) where t=1)"), "`rose`");
        System.out.println(update);

        String sql = "select a.c1,b.c2,c.* from tbl_a a left join tbl_b b on a.c3=b.c3 left join tbl_c c on a.c4=b.c4 where c.c5='tbl_d'";
        System.out.println(Joiner.on(",").join(JSqlParserUtil.getTables(sql)));
    }
}
